#region Using directives

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using Microsoft.SqlServer.Management.Smo;

#endregion

namespace ApplicationLibrary.SqlConnector
{
    /// <summary>
    /// Summary description for SqlManager.
    /// </summary>
    ///

    #region Enums

    public enum UserType
    {
        Guest, User, Super_User, Administrator
    }

    public enum UserStatus
    {
        Found, NotFound, Expired, Locked
    }

    public enum FormType
    {
        Document, Batch, Report
    }

    public enum ProviderType
    {
#if (SQLServer)
#if (SQLCompact || SQLite)
        SQLServer,
#else
        SQLServer
#endif
#endif
#if(SQLCompact)
#if(SQLite)
        SQLCompact,
#else
        SQLCompact
#endif
#endif

#if (SQLite)
        SQLite
#endif
    }

    public enum AuthenticationMode
    {
        Windows, Sql
    }

    #endregion

    public class SqlManager
    {
        public Panel myPanel = null;
        private ConnectionForm myConnectionForm = null;
        private ProviderType MyProvider;
        private string MyHost = string.Empty;
        private string MyDataSource = string.Empty;
        private AuthenticationMode MyAuthentication = AuthenticationMode.Windows;
        private string MyUserID = string.Empty;
        private string MyPassword = string.Empty;
        private bool connected = false;
        protected SqlABConnection MyConnection = null;
        private int currentVersion = 8;

        #region Public Variables

        public string DB_Host { get { return MyHost; } set { MyHost = value; } }

        public string DB_User { get { return MyUserID; } set { MyUserID = value; } }

        public string DB_Password { get { return MyPassword; } set { MyPassword = value; } }

        public string DB_DataSource { get { return MyDataSource; } set { MyDataSource = value; } }

        public ProviderType DB_Provider { get { return MyProvider; } }

        public bool isconnected { get { return connected; } }

        public string DB_ConnectionString
        {
            get
            {
                return SqlManagerString.GetConnectionString(MyProvider, MyHost, MyUserID, MyPassword, MyDataSource, false, false);
            }
        }

        public SqlABConnection DB_Connection
        {
            get { return MyConnection; }
        }

        #endregion

        #region Abstract Method

        //protected abstract bool CreateTable();
        //protected abstract int currentVersion();
        //protected abstract bool UpdateTables(int oldversion, int curversion);

        #endregion

        /// <summary>
        /// SqlManager.
        /// Costruttore per il caso New
        /// </summary>
        ///
        public SqlManager()
        {
        }

        public SqlManager(ProviderType MyProvider, string myhost, string mydatasource, string myuserid, string mypassword)
        {
            this.MyProvider = MyProvider;
            MyHost = myhost;
            MyDataSource = mydatasource;
            MyUserID = myuserid;
            MyPassword = mypassword;
        }

        public bool CreateConnection()
        {
            bool configFound = true;

            if (MyHost.Equals(string.Empty))
                configFound = ReadConfigFile();

            if (configFound)
                ConnectToDatabase();
            else
                CreateNewConnection();

            if (connected && !configFound)
                WriteConfigFile();
            return connected;
        }

        /// <summary>
        /// ConnectToDatabase.
        /// Si connette ad un database esistente
        /// </summary>
        private bool ConnectToDatabase()
        {
            string connectionString = SqlManagerString.GetConnectionString(MyProvider, MyHost, MyUserID, MyPassword, MyDataSource, false, false);

            try
            {
                MyConnection = new SqlABConnection(MyProvider, connectionString);
                MyConnection.Open();
                connected = (MyConnection.State == ConnectionState.Open);
                if (connected)
                    MyConnection.ChangeDatabase(MyDataSource);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), MyDataSource, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }

            if (SearchTable(AM_Version.Name))
            {
                if (MessageBox.Show(Properties.Resources.Database_WrongType,
                                    Properties.Resources.Attention,
                                    MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                {
                    connected = false;
                    return false;
                }
                else
                {
                    RegisterModule RsT = new ApplicationLibrary.ModuleData.RegisterModule();
                    RsT.CreateTable(MyConnection, GlobalInfo.UserInfo.userType);
                    AddAdminUser();
                }
            }
            else
            {
                RegisterModule RsT = new ApplicationLibrary.ModuleData.RegisterModule();
                RsT.CreateTable(MyConnection, GlobalInfo.UserInfo.userType);
            }

            return connected;
        }

        /// <summary>
        /// CreateNewConnection.
        /// Apre la finestra di connessione al database
        /// </summary>
        private bool CreateNewConnection()
        {
            connected = false;

            myConnectionForm = new ConnectionForm();

            DialogResult Result = myConnectionForm.ShowDialog();
            if (Result == DialogResult.OK)
            {
                SetDataBaseParameter();

                if (myConnectionForm.DataBase_NewDatabase)
                {
                    if (CreateNewDatabase())
                        connected = ConnectToDatabase();
                }
                else
                    connected = ConnectToDatabase();
            }

            myConnectionForm.Dispose();
            return connected;
        }

        /// <summary>
        /// CreateNewDatabase.
        /// Crea un nuovo database
        /// </summary>
        private bool CreateNewDatabase()
        {
            string connectionString = SqlManagerString.GetConnectionString(MyProvider, MyHost, MyUserID, MyPassword, MyDataSource, false, true);

            try
            {
                switch (MyProvider)
                {
#if(SQLServer)
                    case ProviderType.SQLServer:
                        Server srv = new Server(MyHost);
                        Database db = new Database(srv, MyDataSource);
                        db.Create();
                        break;
#endif
#if(SQLCompact)
                    case ProviderType.SQLCompact:
                        SqlCeEngine sqlceengine = new SqlCeEngine(connectionString);
                        sqlceengine.CreateDatabase();
                        break;
#endif
#if(SQLite)
                    case ProviderType.SQLite:
                        break;
#endif
                }

                MyConnection = new SqlABConnection(MyProvider, connectionString);
                MyConnection.Open();
                if (MyConnection.State == ConnectionState.Open)
                    MessageBox.Show(Properties.Resources.Database_Create,
                                    MyDataSource,
                                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                MyConnection.ChangeDatabase(MyDataSource);
                RegisterModule RsT = new ApplicationLibrary.ModuleData.RegisterModule();
                RsT.CreateTable(MyConnection, GlobalInfo.UserInfo.userType);
                AddAdminUser();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), MyDataSource, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            if (MyConnection.State == ConnectionState.Open)
                MyConnection.Close();

            return true;
        }


        /// <summary>
        /// NewConnection.
        /// Si connette ad un database esistente
        /// </summary>
        public SqlABConnection NewConnection()
        {
            SqlABConnection newConn = null;
            string connectionString = SqlManagerString.GetConnectionString(MyProvider, MyHost, MyUserID, MyPassword, MyDataSource, false, false);

            try
            {
                newConn = new SqlABConnection(MyProvider, connectionString);
                newConn.Open();
                connected = (newConn.State == ConnectionState.Open);
                if (connected)
                    newConn.ChangeDatabase(MyDataSource);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString(), MyDataSource, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }

            return newConn;
        }

        public void CloseConnection(SqlABConnection connection)
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }

        /// <summary>
        /// SetDataBaseParameter.
        /// Copia i parametri di accesso al database
        /// nelle variabili del SqlConnector
        /// </summary>
        private void SetDataBaseParameter()
        {
            MyProvider = myConnectionForm.DataBase_Provider;
            MyHost = myConnectionForm.DataBase_Host;
            MyDataSource = myConnectionForm.DataBase_Name;
            MyAuthentication = myConnectionForm.DataBase_Authentication;
            MyUserID = myConnectionForm.DataBase_Username;
            MyPassword = myConnectionForm.DataBase_Password;
        }

        private void Dispose(bool disposing)
        {
        }

        /// <summary>
        /// UpdateDataBase
        /// Controlla la versione corrente del database
        /// Se diversa da quella del programma chiama
        /// le routine di aggiornamento
        /// </summary>
        private bool UpdateDatabase()
        {
            int version = ReadDBVersion();
            if (version > currentVersion)
            {
                string message = string.Format(Properties.Resources.Database_WrongVersion,
                                                version, currentVersion);

                MessageBox.Show(message, Properties.Resources.Attention, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                connected = false;
                return false;
            }

            //TODO update database
            //CreateTable();
            //if (version < currentVersion() )
            //    if (UpdateTables(version, currentVersion()))
            //        return UpdateDBVersion();

            return true;
        }

        private int ReadDBVersion()
        {
            SqlABDataReader dr;
            int current = -1;
            try
            {
                QueryBuilder qb = new QueryBuilder();
                qb.AddSelect(AM_Version.Version);
                qb.AddFrom(AM_Version.Name);

                SqlABCommand cmd = new SqlABCommand(qb.Query, MyConnection);
                dr = cmd.ExecuteReader();
                dr.Read();
                current = dr.GetInt32(0);
                dr.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                return current;
            }
            return current;
        }

        protected bool SearchTable(string tablename)
        {
            SqlABDataReader dr;
            bool notfound = false;
            try
            {
                string command = string.Empty; ;
                switch (GlobalInfo.DBaseInfo.dbManager.DB_Provider)
                {
#if(SQLite)
                    case SqlConnector.ProviderType.SQLite:
                        command = string.Format("select tbl_name from sqlite_master where type = 'table' and tbl_name = '{0}'", tablename);
                        break;
#endif
#if(SQLCompact)
                    case SqlConnector.ProviderType.SQLCompact:
                        command = string.Format("select table_name from information_schema.tables where table_name = '{0}'", tablename);
                        break;
#endif
#if(SQLServer)
                    case SqlConnector.ProviderType.SQLServer:
                        command = string.Format("select table_name from information_schema.tables where table_name = '{0}'", tablename);
                        break;
#endif
                }

                //string command = string.Format("select table_name from information_schema.tables where table_name = '{0}'", tablename);

                SqlABCommand cmd = new SqlABCommand(command, MyConnection);
                dr = cmd.ExecuteReader();

                notfound = !dr.Read();
                dr.Close();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
                return true;
            }
            return notfound;
        }

        private bool InsertDBVersion(string module, string version)
        {
            try
            {
                SqlABParameter dbModule = new SqlABParameter("@p1", AM_Version.Module);
                SqlABParameter dbVersion = new SqlABParameter("@p2", AM_Version.Version);

                QueryBuilder qb = new QueryBuilder();
                qb.AddManualQuery("INSERT INTO {0} SET {1}=@p1 WHERE {2}=@p2",
                                       AM_Version.Name, AM_Version.Module, AM_Version.Version);

                SqlABCommand cmd = new SqlABCommand(qb.Query, MyConnection);
                cmd.Parameters.Add(dbModule);
                cmd.Parameters.Add(dbVersion);

                dbModule.Value = module;
                dbVersion.Value = version;
                cmd.ExecuteScalar();
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.Message);
                return false;
            }
            return true;
        }

        private bool UpdateDBVersion(string module, string version)
        {
            try
            {
                SqlABParameter dbVersion = new SqlABParameter("@p1", AM_Version.Version);
                SqlABParameter dbModule = new SqlABParameter("@p2", AM_Version.Module);

                QueryBuilder qb = new QueryBuilder();
                qb.AddManualQuery("UPDATE {0} SET {1}=@p1 WHERE {2}=@p2",
                                       AM_Version.Name, AM_Version.Version, AM_Version.Module);

                SqlABCommand cmd = new SqlABCommand(qb.Query, MyConnection);
                cmd.Parameters.Add(dbVersion);

                dbVersion.Value = version;
                dbModule.Value = module;
                cmd.ExecuteScalar();
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.Message);
                return false;
            }
            return true;
        }

        private bool CreateDBVersion()
        {
            try
            {
                SqlABParameter dbVersion = new SqlABParameter("@p1", AM_Version.Version);
                string command = "INSERT INTO " + AM_Version.Name + " ( " +
                    AM_Version.Version + " ) " +
                    "VALUES (@p1)";

                SqlABCommand cmd = new SqlABCommand(command, MyConnection);
                cmd.Parameters.Add(dbVersion);

                dbVersion.Value = currentVersion;
                cmd.ExecuteScalar();
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.Message);
                return false;
            }
            return true;
        }

        private bool AddAdminUser()
        {
            return AddUser("Administrator", "a", "", UserType.Administrator);
        }

        public bool AddUser(string username, string password, string surname, UserType usertype)
        {
            try
            {
                SqlABParameter dbUser = new SqlABParameter("@p1", AM_Users.Username);
                SqlABParameter dbPass = new SqlABParameter("@p2", AM_Users.Password);
                SqlABParameter dbSurn = new SqlABParameter("@p3", AM_Users.Surname);
                SqlABParameter dbPriv = new SqlABParameter("@p4", AM_Users.UserType);
                SqlABParameter dbExp = new SqlABParameter("@p5", AM_Users.Expired);
                SqlABParameter dbExpD = new SqlABParameter("@p6", AM_Users.ExpireDate);
                SqlABParameter dbCPwd = new SqlABParameter("@p7", AM_Users.ChangePassword);
                SqlABParameter dbLock = new SqlABParameter("@p8", AM_Users.Blocked);

                string command = "INSERT INTO " + AM_Users.Name + " ( " +
                    AM_Users.Username + ", " +
                    AM_Users.Password + ", " +
                    AM_Users.Surname + ", " +
                    AM_Users.UserType + ", " +
                    AM_Users.Expired + ", " +
                    AM_Users.ExpireDate + ", " +
                    AM_Users.ChangePassword + ", " +
                    AM_Users.Blocked + " ) " +
                    "VALUES (@p1,@p2,@p3,@p4,@p5,@p6,@p7, @p8)";

                SqlABCommand cmd = new SqlABCommand(command, MyConnection);
                cmd.Parameters.Add(dbUser);
                cmd.Parameters.Add(dbPass);
                cmd.Parameters.Add(dbSurn);
                cmd.Parameters.Add(dbPriv);
                cmd.Parameters.Add(dbExp);
                cmd.Parameters.Add(dbExpD);
                cmd.Parameters.Add(dbCPwd);
                cmd.Parameters.Add(dbLock);

                dbUser.Value = username;
                dbPass.Value = Cryption.Encrypt(password);
                dbSurn.Value = surname;
                dbPriv.Value = usertype;
                dbExp.Value = 0;
                dbExpD.Value = DateTime.Today;
                dbCPwd.Value = 1;
                dbLock.Value = 0;

                cmd.ExecuteScalar();
            }
            catch (SqlException exc)
            {
                MessageBox.Show(exc.Message);
                return false;
            }
            return true;
        }

        private bool ReadConfigFile()
        {
            bool loaded = false;

            try
            {
                XmlDocument config = new XmlDocument();
                config.Load(ConfigFilename);

                MyProvider = (ProviderType)Enum.Parse(typeof(ProviderType), config.SelectSingleNode(SS.Login + "/" + SS.ProviderType).InnerText);
                MyHost = config.SelectSingleNode(SS.Login + "/" + SS.Host).InnerText;
                MyDataSource = config.SelectSingleNode(SS.Login + "/" + SS.DataSource).InnerText;
                MyAuthentication = (AuthenticationMode)Enum.Parse(typeof(AuthenticationMode), config.SelectSingleNode(SS.Login + "/" + SS.AuthenticationType).InnerText);
                MyUserID = config.SelectSingleNode(SS.Login + "/" + SS.UserName).InnerText;
                MyPassword = Cryption.Decrypt(config.SelectSingleNode(SS.Login + "/" + SS.Password).InnerText);
                XmlNode userNode = config.SelectSingleNode(SS.Login + "/" + SS.UsersList);
                if (userNode != null)
                {
                    XmlNodeList userList = userNode.SelectNodes(SS.User);
                    GlobalInfo.UsersLogged = new string[userList.Count];
                    for (int t = 0; t < userList.Count; t++)
                        GlobalInfo.UsersLogged[t] = userList[t].InnerText;
                }

                loaded = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                loaded = false;
            }

            return loaded;
        }

        public void WriteConfigFile()
        {
            XmlDocument config = new XmlDocument();

            config.AppendChild(config.CreateComment(Properties.Resources.Xml_Setting));
            config.AppendChild(config.CreateComment(Properties.Resources.Xml_Delete));

            XmlNode Login = config.CreateElement(SS.Login);
            config.AppendChild(Login);

            XmlNode ProviderType = config.CreateElement(SS.ProviderType);
            ProviderType.InnerText = MyProvider.ToString();
            Login.AppendChild(ProviderType);

            XmlNode Host = config.CreateElement(SS.Host);
            Host.InnerText = MyHost;
            Login.AppendChild(Host);

            XmlNode DataSource = config.CreateElement(SS.DataSource);
            DataSource.InnerText = MyDataSource;
            Login.AppendChild(DataSource);

            XmlNode Authentication = config.CreateElement(SS.AuthenticationType);
            Authentication.InnerText = MyAuthentication.ToString();
            Login.AppendChild(Authentication);

            XmlNode UserName = config.CreateElement(SS.UserName);
            UserName.InnerText = MyUserID;
            Login.AppendChild(UserName);

            XmlNode Password = config.CreateElement(SS.Password);
            Password.InnerText = Cryption.Encrypt(MyPassword);
            Login.AppendChild(Password);

            XmlNode UserList = config.CreateElement(SS.UsersList);
            Login.AppendChild(UserList);

            if (!System.IO.Directory.Exists(ConfigDirectory))
                System.IO.Directory.CreateDirectory(ConfigDirectory);

            config.Save(ConfigFilename);
        }

        public void UpdateConfigFile(string user)
        {
            bool bFound = false;
            for (int t = 0; GlobalInfo.UsersLogged != null && t < GlobalInfo.UsersLogged.Length && !bFound; t++)
                bFound |= GlobalInfo.UsersLogged[t] == user;

            if (!bFound)
            {
                XmlDocument config = new XmlDocument();
                config.Load(ConfigFilename);
                XmlNode UserList = config.SelectSingleNode(SS.Login + "/" + SS.UsersList);
                XmlNode User = config.CreateElement(SS.User);
                User.InnerText = user;
                UserList.AppendChild(User);
                config.Save(ConfigFilename);
            }
        }

        private string ConfigFilename
        {
            get
            {
                return System.IO.Path.Combine(ConfigDirectory, SS.LoginFile);
            }
        }

        private string ConfigDirectory
        {
            get
            {
                string directory = string.Empty;
                if (Directory.GetParent(Directory.GetCurrentDirectory()).FullName.EndsWith("bin", StringComparison.CurrentCultureIgnoreCase))
                    directory = new DirectoryInfo(Environment.CurrentDirectory).Parent.Parent.Parent.Name;
                else
                    directory = new DirectoryInfo(Environment.CurrentDirectory).Name;

                return System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), directory);
            }
        }
    }

    public class SqlManagerString
    {
        public static string GetConnectionString
            (
            ProviderType provider,
            string server,
            string user,
            string password,
            string catalog,
            bool winNtAuthent,
            bool isNew
            )
        {
            string connectionString = string.Empty;

            switch (provider)
            {
#if(SQLServer)
                case ProviderType.SQLServer:
                    connectionString = (winNtAuthent)
                                ? string.Format(NameSolverDatabaseStrings.SQLWinNtConnection, server, catalog)
                                : string.Format(NameSolverDatabaseStrings.SQLConnection, server, catalog, user, password);
                    break;
#endif
#if(SQLCompact)
                case ProviderType.SQLCompact:
                    connectionString = string.Format(NameSolverDatabaseStrings.SQLCompactConnection, catalog, password);
                    break;
#endif
#if(SQLite)

                // Data Source=mydb.db;Version=3;New=True;
                case ProviderType.SQLite:
                    connectionString = isNew
                                ? string.Format(NameSolverDatabaseStrings.SQLiteConnectionNew, catalog, "True")
                                : string.Format(NameSolverDatabaseStrings.SQLiteConnection, catalog);
                    break;
#endif
            }

            return connectionString;
        }
    }

    public class NameSolverDatabaseStrings
    {
        public const string SQLSqlProvider = "SQLSERVER";

        public const string ProviderConnAttribute = "Provider={0}; ";
        public const string UnknownDBMS = "Unknown DBMS {0}; ";

        public const string SQLWinNtConnection = "Data Source={0};Initial Catalog='{1}';Integrated Security='SSPI';Connect Timeout=30; Pooling= false ";
        public const string SQLConnection = "Data Source={0};Initial Catalog='{1}'; MultipleActiveResultSets=True; User ID='{2}';Password='{3}';";
        public const string SQLCompactConnection = "Data Source={0};Password='{1}';";
        public const string SQLiteConnectionNew = "Data Source={0};Version=3;New={1};Compress=True;";
        public const string SQLiteConnection = "Data Source={0};Version=3;Compress=True;";

        // Data Source=mydb.db;Version=3;New=True;

        public const string SQLCreationRemote = "CREATE DATABASE {0}";
    }

    public struct SS
    {
        public const string LoginFile = "loginsetting.config";
        public const string ProviderType = "ProviderType";
        public const string AuthenticationType = "AuthenticationType";
        public const string Login = "Login";
        public const string Host = "Host";
        public const string DataSource = "DataSource";
        public const string Directory = "ApplicationBuilder";

        public const string Users = "Users";
        public const string User = "User";
        public const string UserName = "UserName";
        public const string Password = "Password";
        public const string UsersList = "UsersList";

        public const string IdApplication = "GuidApplication";
    }

    #region TableReader

    public abstract class DataReaderUpdater
    {
        protected SqlABConnection sqlCN = null;
        protected SqlABDataAdapter sqlDA = null;
        protected SqlABCommand sqlCM = null;
        protected DataSet dataSet = null;
        protected string myTable = string.Empty;
        private string myCode = string.Empty;
        protected bool isUpdater = false;

        #region Public Variables

        public SqlABConnection Connection
        {
            get { return sqlCN; }
            set { sqlCN = value; }
        }

        public string Table
        {
            get { return myTable; }
            set { myTable = value; }
        }

        public string Code
        {
            get { return myCode; }
            set { myCode = value; }
        }

        public int Count
        {
            get
            {
                if (dataSet == null) return -1;

                return dataSet.Tables[myTable].Rows.Count;
            }
        }

        #endregion

        public DataReaderUpdater()
            : this(GlobalInfo.DBaseInfo.dbManager.DB_Connection, false)
        {
        }

        public DataReaderUpdater(bool updater)
            : this(GlobalInfo.DBaseInfo.dbManager.DB_Connection, updater)
        {
        }

        public DataReaderUpdater(SqlABConnection conn, bool updater)
        {
            Debug.Assert(conn != null, "Connection is null");
            sqlCN = conn;
            isUpdater = updater;
        }

        public virtual bool Find()
        {
            if (dataSet == null)
            {
                dataSet = new DataSet();
                CreateConnection();
                if (isUpdater)
                    CreateUpdateCommand();
            }

            dataSet.Clear();
            SetParameters();
            try
            {
                sqlDA.Fill(dataSet, myTable);
            }
            catch (SqlException e)
            {
                Trace.Write(e.Message);
                Debug.Assert(false);
            }

            return dataSet.Tables[0].Rows.Count > 0;
        }

        protected abstract void SetParameters();

        protected abstract void CreateConnection();

        public virtual DataRow AddRecord()
        {
            DataRow dr = dataSet.Tables[myTable].NewRow();
            dataSet.Tables[myTable].Rows.Add(dr);
            return dr;
        }

        public bool Update()
        {
            try
            {
                DataSet ds2 = dataSet.GetChanges();
                if (ds2 != null)
                {
                    if (ds2.HasErrors) MessageBox.Show("Errori");
                    if (ds2.HasChanges())
                        sqlDA.Update(dataSet, myTable);
                }
            }
            catch (SqlException e)
            {
                Trace.Write(e.Message);
                Debug.Assert(false);
                return false;
            }

            return true;
        }

        public bool Delete()
        {
            try
            {
                dataSet.Tables[myTable].Rows[0].Delete();
                sqlDA.Update(dataSet, myTable);
            }
            catch (SqlException e)
            {
                Trace.Write(e.Message);
                Debug.Assert(false);
                return false;
            }

            return true;
        }

        public DataRow GetRecord()
        {
            if (dataSet.Tables[myTable].Rows.Count > 0)
                return dataSet.Tables[myTable].Rows[0];
            else
                return null;
        }

        public DataRow GetRecord(int i)
        {
            if (i < dataSet.Tables[myTable].Rows.Count)
                return dataSet.Tables[myTable].Rows[i];
            else
                return null;
        }

        public T GetValue<T>(iColumn columnname)
        {
            return GetValue<T>(columnname.Name, 0);
        }

        public T GetValue<T>(iColumn columnname, int i)
        {
            return GetValue<T>(columnname.Name, i);
        }

        public T GetValue<T>(string columnname, int i)
        {
            if (i < dataSet.Tables[myTable].Rows.Count)
            {
                //if (dataSet.Tables[myTable].Rows[i][columnname] == System.DBNull.Value)
                //    return default(T);

                if (typeof(T).BaseType == typeof(Enum))
                {
                    return dataSet.Tables[myTable].Rows[i][columnname] == System.DBNull.Value
                        ? (T)Enum.ToObject(typeof(T), default(T))
                        : (T)Enum.ToObject(typeof(T), dataSet.Tables[myTable].Rows[i][columnname]);
                }
                else
                {
                    return dataSet.Tables[myTable].Rows[i][columnname] == System.DBNull.Value
                            ? (
                                typeof(T) == typeof(string)
                                    ? (T)Convert.ChangeType("", typeof(T))
                                    : (T)Convert.ChangeType(default(T), typeof(T))
                              )
                        : (T)Convert.ChangeType(dataSet.Tables[myTable].Rows[i][columnname], typeof(T));
                }
            }
            else
                return default(T);
        }

        public void SetValue<T>(iColumn columnname, int row, T value)
        {
            SetValue<T>(columnname.Name, row, value);
        }

        public void SetValue<T>(iColumn columnname, T value)
        {
            SetValue<T>(columnname.Name, 0, value);
        }

        public void SetValue<T>(string columnname, int row, T value)
        {
            Debug.Assert(dataSet.Tables[myTable].Rows.Count > row, "Row number exceeed number of rows");
            if (dataSet.Tables[myTable].Rows.Count > 0)
                dataSet.Tables[myTable].Rows[row][columnname] = value;
        }

        protected virtual void CreateUpdateCommand()
        {
            SqlABCommandBuilder cBuilder = new SqlABCommandBuilder(sqlDA);
            cBuilder.QuotePrefix = "[";
            cBuilder.QuoteSuffix = "]";
            sqlDA.UpdateCommand = cBuilder.GetUpdateCommand();
            sqlDA.InsertCommand = cBuilder.GetInsertCommand();
            sqlDA.DeleteCommand = cBuilder.GetDeleteCommand();
        }
    }

    #endregion

    public class DataRowValues
    {
        public static void SetValue<T>(DataRow row, iColumn col, T value)
        {
            row[col.Name] = value;
        }

        public static T GetValue<T>(DataRow row, iColumn col)
        {
            if (row[col.Name] != System.DBNull.Value && row[col.Name] != null)
                return (T)Convert.ChangeType(row[col.Name], typeof(T));
            else
                return default(T);
        }
    }
}